package DAL.JDBC;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

/* This class is responsible to connect to the database 
 * and evey other class which use the DB should get an handle
 * to the connection using this class.
 * IT HAS ONLY STATIC MEMEBRS 
 */
public class DBConn{
	
	static Connection conn;
	/* user name and pwd only for local testing*/
	private static final String USER_NAME	= "root";
	private static final String PASSWORD	= "";

	  // private DataSource dataSource;
	   //private JdbcTemplate jdbcTemplateObject;
	   
	static
	{
	
	try
    {
        System.out.println("Loading the driver...");
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Loading successed  ---------------");
        
        String jsonEnvVars = java.lang.System.getenv("VCAP_SERVICES");
        if(jsonEnvVars != null){
			parseUrlFromEnvVarsAndConnect(jsonEnvVars); 
		}
		else{
			//Runs locally - only for maintenance 
			
			String url = "jdbc:mysql://localhost/console_store";
			System.out.println("Connected local host url="+url);
			conn= DriverManager.getConnection(url, USER_NAME, PASSWORD);
		}
        createTables();
		createDefaultData();

        System.out.println((new StringBuilder("conn successed. conn=")).append(conn).toString());
    }
    catch(ClassNotFoundException ex)
    {
        System.err.println((new StringBuilder("error loading:")).append(ex.getMessage()).toString());
    }
    catch(SQLException ex)
    {
        System.err.println((new StringBuilder("error loading2:")).append(ex.getMessage()).toString());
    }

}
    private static void parseUrlFromEnvVarsAndConnect(String jsonEnvVars) {
		String url = "";
		try {
			JSONObject jsonObject = new JSONObject(jsonEnvVars);
			JSONArray jsonArray = jsonObject.getJSONArray("mysql-5.1");
			jsonObject = jsonArray.getJSONObject(1);
			jsonObject = jsonObject.getJSONObject("credentials");
			String host 	=  jsonObject.getString("host");
			System.out.println("parseUrlFromEnvVarsAndConnect host="+host);
			Integer p = jsonObject.getInt("port");
			String port 	=  p.toString();
			System.out.println("parseUrlFromEnvVarsAndConnect port="+port);
			String dbName 	=  jsonObject.getString("name");
			System.out.println("parseUrlFromEnvVarsAndConnect dbName="+dbName);
			String username =  jsonObject.getString("username");
			System.out.println("parseUrlFromEnvVarsAndConnect username="+username);
			String password =  jsonObject.getString("password");
			
			System.out.println("parseUrlFromEnvVarsAndConnect password="+password);

			//url = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
			url = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
			//jdbc:mysql://localhost/console_store
			conn = DriverManager.getConnection(url, username, password);
		} 
		catch (JSONException e) {
			System.err.println("Conn.connect: " + e.getMessage());
		}
		catch (SQLException e){
			System.err.println("Conn.connect: " + e.getMessage());
		}
	}
    
    public static Connection getConnection(){
    	try {
			if(!conn.isValid(0))
			{
				System.out.println("TIMEOUT!!!!!");
				conn = DBConn.resetConn();
			}
			else
			{
				System.out.println("VALID!!!!!");
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
    	return conn;
    }
    
    public static Connection resetConn()
    {
    	System.out.println("restarting connection");
    	String jsonEnvVars = java.lang.System.getenv("VCAP_SERVICES");
    	parseUrlFromEnvVarsAndConnect(jsonEnvVars);
    	return conn;
    }
    
    private static void createTables()
    {
    	System.out.println("trying to create tables");
    	/*create users tabel*/
    	String DropSQL = "DROP TABLE if exists users";
    	String SQL = "CREATE TABLE IF NOT EXISTS `users` (`Id` int(11) NOT NULL AUTO_INCREMENT,`FirstName` varchar(50) NOT NULL,`LastName` varchar(50) NOT NULL,`Email` varchar(50) NOT NULL,`Phone` varchar(50) NOT NULL,`Address` text NOT NULL,`Password` varchar(50) NOT NULL,`CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`Id`), UNIQUE KEY `Email` (`Email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create used games tabel*/
    	DropSQL = "DROP TABLE if exists usedgames";
    	SQL = "CREATE TABLE IF NOT EXISTS `usedgames` (`Id` int(11) NOT NULL AUTO_INCREMENT,`UserID` int(11) NOT NULL,`Name` varchar(30) NOT NULL, `Description` text NOT NULL,`CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

    	/*create recommendation tabel*/
    	DropSQL = "DROP TABLE if exists recommendations";
    	SQL = "CREATE TABLE IF NOT EXISTS `recommendations` (`Id` int(11) NOT NULL AUTO_INCREMENT,`UserID` int(11) NOT NULL,`GameID` int(11) NOT NULL,`Recommendation` text NOT NULL,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create ranks tabel*/
    	DropSQL = "DROP TABLE if exists ranks";
    	SQL = "CREATE TABLE IF NOT EXISTS `ranks` (`Id` int(11) NOT NULL AUTO_INCREMENT,`UserID` int(11) NOT NULL,`GameID` int(11) NOT NULL,`Rank` int(1) NOT NULL,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create consoleTypes tabel*/
    	DropSQL = "DROP TABLE if exists consoletypes";
    	SQL = "CREATE TABLE IF NOT EXISTS `consoletypes` (`Id` int(11) NOT NULL AUTO_INCREMENT,`ConsoleName` varchar(50) NOT NULL,PRIMARY KEY (`Id`), UNIQUE KEY `ConsoleName` (`ConsoleName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create Genres tabel*/
    	DropSQL = "DROP TABLE if exists genres";
    	SQL = "CREATE TABLE IF NOT EXISTS `genres` (`Id` int(11) NOT NULL AUTO_INCREMENT,`GenreName` varchar(50) NOT NULL,PRIMARY KEY (`Id`), UNIQUE KEY `GenreName` (`GenreName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create games tabel*/
    	DropSQL = "DROP TABLE if exists games";
    	SQL = "CREATE TABLE IF NOT EXISTS `games` (`Id` int(11) NOT NULL AUTO_INCREMENT,`Name` varchar(50) NOT NULL,`Description` text NOT NULL,`TrailerURL` varchar(50) NOT NULL, `ConsoleID` int(11) NOT NULL, `GenreID` int(11) NOT NULL, `Quantity` int(11) NOT NULL, `Price` float(11) NOT NULL,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create game images tabel*/
    	DropSQL = "DROP TABLE if exists gameimages";
    	SQL = "CREATE TABLE IF NOT EXISTS `gameimages` (`GameID` int(11) NOT NULL ,`image` text NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create order items tabel*/
    	DropSQL = "DROP TABLE if exists orderitems";
    	SQL = "CREATE TABLE IF NOT EXISTS `orderitems` (`OrderID` int(11) NOT NULL ,`GameID` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	/*create orders tabel*/
    	DropSQL = "DROP TABLE if exists orders";
    	SQL = "CREATE TABLE IF NOT EXISTS `orders` (`Id` int(11) NOT NULL AUTO_INCREMENT,`CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`UserID` int(11) NOT NULL,`CreditCard` int(11) NOT NULL,`Feedback` text,PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    	try {
			Statement st = conn.createStatement();
			st.execute(DropSQL);
			st.execute(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	System.out.println("finishing to create tables");

    }
    
    private static void createDefaultData()
    {
    	String SQL = "insert into users values (null, 'user', 'test', 'admin@store.com', '052-5375146', 'habrosh 6 kiryat ono', 'password', now())";
    	String SQL2 = "insert into users values (null, 'user2', 'test2', 'user@store.com', '052-5375146', 'habrosh 6 kiryat ono', 'password', now())";
    	String DefaultInsertToGenres = "insert into genres values (null, 'Action'), (null, 'Sports'), (null, 'Advetures'), (null, 'Shooters'), (null, 'Other')";
    	String DefaultInsertToConsoles = "insert into consoletypes values (null, 'Gameboy'), (null, 'Nintendo'), (null, 'PlayStation 2'), (null, 'PlayStation 3'), (null, 'PSP'), (null, 'Wii'), (null, 'XBOX360')";
    	String[] DefaultInsertToGames = {
    			"insert into games values (null, 'Fifa 13', 'FIFA 13 is the 2012 release of EA\\'s soccer game franchise and is driven by five innovations involving artificial intelligence, dribbling, ball control and physical play.', 'http://www.youtube.com/watch?v=AZpwgRFQXb8', 7, 2, 10, 300)",
    			"insert into games values (null, 'BattleField 3', 'Step into the role of elite US Marines in Battlefield 3. You will experience single-player missions, as well as competitive multiplayer action, in diverse locations around the world. You will get to experience fighting on claustrophobic streets, metropolitan downtowns, and vehicle-friendly landscapes in the US, Europe, and the Middle East. This version of Battlefield is powered by a new innovative technology call Frostbite 2, which delivers superior visual quality, a grand scale, massive destruction, lifelike character animations, and dynamic audio. Watch as bullets fly by, walls crumble, and explosions throw you to the ground. In addition, Battlefield 3 offers unparalleled vehicular warfare. Experience all sorts of vehicle types for land, air, and sea. Battlefield 3 will be available on the Xbox 360, the PlayStation 3, and PC.', 'http://www.youtube.com/watch?v=2zw8SmsovJc', 7, 4, 10, 450 )",
    			"insert into games values (null, 'Sim City 5', 'SimCity is a city-building simulation game that lets players create the city they desire by giving you the power to make the choices that shape your city and power the citizens within it.', 'http://www.youtube.com/watch?v=2830eSBU3uA', 5, 5, 10, 99)",
    			"insert into games values (null, 'Diablo III', 'Blizzard\\'s hack-and-slash lootfest Diablo III is coming to the PS3 and PS4 and will include split-screen and four-player co-op.', 'http://www.youtube.com/watch?v=VsdVmt6193A', 4, 1, 10, 99)",
    			"insert into games values (null, 'NBA 2k13', 'NBA 2K14 is a basketball gaming experience from 2K Sports.', 'http://www.youtube.com/watch?v=ADfjrh12L38', 1, 2, 10, 299)",
				"insert into games values (null, 'Super Mario', 'Super Mario 3D World is the first multiplayer 3D Mario game for the Wii U where players can choose to play as Mario, Luigi, Princess Peach, or Toad.', 'http://www.youtube.com/watch?v=JooUqPF2Lf4', 6, 3, 10, 199)"
    	};
		
		String[] DefaultInsertToImages = {
    			"insert into gameimages values (1, 'images/games/f1.jpg')",
    			"insert into gameimages values (1, 'images/games/f2.jpg')",
    			"insert into gameimages values (1, 'images/games/f3.jpg')",
    			"insert into gameimages values (2, 'images/games/b1.jpg')",
    			"insert into gameimages values (2, 'images/games/b2.jpg')",
    			"insert into gameimages values (2, 'images/games/b3.jpg')",
    			"insert into gameimages values (3, 'images/games/s1.jpg')",
    			"insert into gameimages values (3, 'images/games/s2.jpg')",
    			"insert into gameimages values (3, 'images/games/s3.jpg')",
    			"insert into gameimages values (4, 'images/games/d1.jpg')",
    			"insert into gameimages values (4, 'images/games/d2.jpg')",
    			"insert into gameimages values (4, 'images/games/d3.jpg')",
    			"insert into gameimages values (5, 'images/games/MJ_fly_dunk.jpg')",
    			"insert into gameimages values (5, 'images/games/Everything610.jpg')",
    			"insert into gameimages values (5, 'images/games/NBA2K14_1.jpeg')",
    			"insert into gameimages values (5, 'images/games/NBA2K14_2.jpeg')",
    			"insert into gameimages values (5, 'images/games/nba-xenon-0092.jpg')",
    			"insert into gameimages values (6, 'images/games/sm1.jpg')",
    			"insert into gameimages values (6, 'images/games/sm2.jpg')",
    			"insert into gameimages values (6, 'images/games/sm3.jpg')",
    	};
		
		String[] DefaultInsertToRanks = {
				"insert into ranks values (null,1,1,1)",
				"insert into ranks values (null,1,2,1)",
				"insert into ranks values (null,1,3,1)",
				"insert into ranks values (null,1,4,1)",
				"insert into ranks values (null,2,1,3)",
				"insert into ranks values (null,2,2,2)",
				"insert into ranks values (null,2,3,1)",
				"insert into ranks values (null,2,4,5)"
		};

		String[] DefaultInsertToRecs = {
				"insert into recommendations values (null,1,1,'This is very good game')",
				"insert into recommendations values (null,1,2,'This is very good game')",
				"insert into recommendations values (null,1,3,'This is very good game')",
				"insert into recommendations values (null,1,4,'This is very good game')",
				"insert into recommendations values (null,2,1,'This is very bad game')",
				"insert into recommendations values (null,2,2,'This is very bad game')",
				"insert into recommendations values (null,2,3,'This is very bad game')",
				"insert into recommendations values (null,2,4,'This is very bad game')"
		};
		
		String[] DefaultInsertToUsedGames = {
				"insert into usedgames values (null,1,'3 Games', 'i am  selling 3 games' , now())",
				"insert into usedgames values (null,2,'Kinect games', 'i am  selling a kinect games. call me: 0525375146' , now())",
				"insert into usedgames values (null,1,'Call Of Duty', 'i am  selling Call Of Duty, Like New' , now())"
		};
    			
    	try {
			Statement st = conn.createStatement();
			st.execute(SQL);
			st.execute(SQL2);
			st.execute(DefaultInsertToConsoles);
			st.execute(DefaultInsertToGenres);
			
			for (String sql : DefaultInsertToGames) {
				st.execute(sql);
			}
			
			for (String sql : DefaultInsertToImages) {
				st.execute(sql);
			}
			
			for (String sql : DefaultInsertToRanks) {
				st.execute(sql);
			}
			
			for (String sql : DefaultInsertToRecs) {
				st.execute(sql);
			}
			System.out.println("try to insert to used");
			for (String sql : DefaultInsertToUsedGames) {
				st.execute(sql);
			}System.out.println("try to insert to used");
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

    }

}
